﻿/****************************** Module Header ******************************\
* Module Name:  AzureMLPredict.cs
* Project:      AzureMLPrediction
* Copyright (c) Microsoft Corporation.
* 
* The AzureMLPredict project is a class library project written in C#.
* It illustrates how to use Request/Response REST API generated as part of Azure ML Studio
* as part of a managed COM component Excel Add In which can be used as an
* Automation AddIn in Excel. 
* 
* This source is subject to the Microsoft Public License.
* See http://www.microsoft.com/en-us/openness/resources/licenses.aspx#MPL.
* All other rights reserved.
* 
* THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, 
* EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED 
* WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
\***************************************************************************/

#region directives
using System;
using System.IO;
using System.Text;
using Microsoft.Win32;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
using Microsoft.Office.Tools.Excel;
using System.Net;
using System.Text.RegularExpressions;
#endregion

namespace AzureMLPrediction
{
    [Guid("AFD424D5-61D1-49b8-8196-23E0C09B15EB")]
    [ClassInterface(ClassInterfaceType.AutoDual)]
    [ComVisible(true)]
    public class AzureMLPredict
    {

        #region publicvars
        private string helpuri;
        private string predictionuri;
        private string jsonschema;
        private int colnum;
        private string authkey;
        private Excel.Range inputRange;
        private string outval;
        private string errval;
        private bool rowwise;
        #endregion

        #region private prediction functions
        /// <summary>
        /// Retrieve JSON schema from API help endpoint by parsing HTML returned by the API help.
        /// </summary>
        /// <returns>None. Stores output in the global variable jsonschema</returns>
        private void getJsonFromHttpHelp()
        {

            WebRequest request;
            Stream result;
            try
            {
                // retrieve API help from the user's request/response end point
                request = WebRequest.Create(helpuri);
                request.Method = "GET";
                request.ContentType = "application/json; charset=utf-8";
                WebResponse res = request.GetResponse();
                result = res.GetResponseStream();
            }
            catch (Exception e1)
            {
                errval = ("Failed to connect to REST API endpoint: " + e1.Message + ". Please check the URL");
                return;
            }

            // start forming JSON input 
            string jsonfmt = "{  \"Id\": \"score00001\",  \"Instance\": {    \"FeatureVector\": {";
            string newline;

            using (StreamReader sr = new StreamReader(result))
            {

                while ((newline = sr.ReadLine()) != null)
                {
                    string[] parm;
                    string[] paramDelimiter = { "Parameter Name=" };
                    string[] removeTrailingDelimiter = { " Type" };

                    // retrieve column names from odata endpoint
                    parm = newline.Split(paramDelimiter, StringSplitOptions.RemoveEmptyEntries);
                    if (parm.Length > 1)
                    {
                        jsonfmt += (parm[1].Split(removeTrailingDelimiter, StringSplitOptions.RemoveEmptyEntries))[0];
                        jsonfmt += ": \"0\",";
                    }
                }

            }
            // close JSON schema
            jsonfmt = jsonfmt.Substring(0, jsonfmt.Length - 1);
            jsonfmt += "},    \"GlobalParameters\": {}  }}";
            jsonschema = jsonfmt;

        }

        /// <summary>
        /// Create the JSON data to be sent as part of POST request parameters by finding the
        /// position in schema and replacing 0 in schema with real input value from excel range
        /// </summary>
        /// <param name="jsonStr1">JSON schema retrieved from API help contents</param>
        /// <param name="currRow">currRow represents the input row within the Excel Range to be used for prediction </param>
        /// <returns>None. Stores the output in the global variable outputval</returns>
        private void doPredictions(string jsonStr1, int currRow)
        {
            // remove any whitespace from JSON schema
            string jsonStr = jsonStr1.Trim();
            //jsonStr = Regex.Replace(jsonStr, " ", string.Empty);
            byte[] byteArray = Encoding.UTF8.GetBytes(jsonStr);
            MemoryStream stream = new MemoryStream(byteArray);
            string result = "";
            char nextChar;
            bool done = false;
            int counter = jsonStr.Length;

            using (StreamReader sr = new StreamReader(stream))
            {
                bool fvst = false;
                // initialize the markers to be used for detecting postions to add input parameters
                string cmpstr = "\"FeatureVector\"";
                int fvlen = cmpstr.Length;
                string cmpstr1 = "\"score000\"";
                int idlen = cmpstr1.Length;

                int currCol = 1;
                while ((!done) && (!sr.EndOfStream) && (counter-- >= 0))
                {
                    string nextChStr;
                    nextChar = (char)sr.Read();
                    nextChStr = nextChar.ToString();

                    if ((nextChar.Equals(':')))
                    {
                        if (result.Length > fvlen)
                        {
                            if (result.Substring(result.Length - fvlen, fvlen).ToLower().Equals(cmpstr.ToLower()))
                            {
                                // found feature vector marker - set the marker found varaible to true
                                fvst = true;
                            }
                        }
                    }
                    if ((fvst == true) && (nextChar == '0'))
                    {
                        // found the input value position. Update schema with input value from the excel range
                        string inpvalstr = (this.rowwise) ? ((inputRange.Cells[currRow + 1, currCol]).Value2.ToString()) : ((inputRange.Cells[currCol, currRow + 1]).Value2.ToString());
                        string currval = inpvalstr;
                        nextChStr = currval;
                        currCol++;
                    }
                    result += nextChStr;
                }

            }
            // call the function to retrieve prediction from the REST API using the JSON data created above
            getPrediction(result, currRow);
        }
        /// <summary>
        /// Retrieve the prediction by calling API with the JSON data passed as parameter
        /// </summary>
        /// <param name="postData">JSON schema to be sent as Input to the REST API</param>
        /// <param name="currRow">currRow represents the input row within the Excel Range to be used for prediction </param>
        /// <returns>None. Stores the output in the global variable outputval</returns>
        private void getPrediction(string postData, int currRow)
        {
            WebRequest request = null;

            try
            {
                request = WebRequest.Create(predictionuri);
                request.Method = "POST";
                request.ContentType = "application/json";
                if (authkey != "")
                {
                    // request.Headers.Add("Authorization:Bearer", authkey);
                    request.Headers.Add(HttpRequestHeader.Authorization,
                        string.Format("Bearer {0}", authkey));
                }


                //get a reference to the request-stream, and write the postData to it
                using (Stream s = request.GetRequestStream())
                {
                    using (StreamWriter sw = new StreamWriter(s))
                        sw.Write(postData);
                }

                //get response-stream, and use a streamReader to read the content
                using (Stream s = request.GetResponse().GetResponseStream())
                {
                    using (StreamReader sr = new StreamReader(s))
                    {
                        string jsonData = sr.ReadToEnd();
                        int columns = (rowwise) ? (inputRange.Columns.Count) : (inputRange.Rows.Count);
                        jsonData = Regex.Replace(jsonData, "\"", string.Empty);
                        string[] predVals = jsonData.Split(',');
                        int maxVals = predVals.Length;
                        int start = ((colnum > 0) && (colnum < maxVals)) ? (maxVals - colnum) : (0);

                        // go over the response and retrieve the columns to be returned
                        outval = "";
                        for (int i = start; i < maxVals; i++)
                        {
                            if ((i > start) && (i <= maxVals - 1))
                            {
                                // add delimiter between the columns
                                outval += ", ";
                            }

                            string val = Regex.Replace(predVals[i], "]", string.Empty);

                            if (val.Contains("\x5b"))
                            {
                                val = Regex.Replace(val, "\\[", string.Empty);
                            }
                            outval += val;

                        }

                    }
                }
            }
            catch (Exception e1)
            {

                errval = ("Failed to connect to prediction REST API endpoint: " + e1.Message + ". Please check " + predictionuri + " URL.");
                return;

            }
        }
        /// <summary>
        /// Converts REST API URL to Odata API URL for retrieving API schema
        /// </summary>
        /// <param name="uri1">REST API URI</param>
        /// <returns>ODATA URI</returns>
        private string convertRestToOdata(string uri1)
        {
            string[] del = { "/" };
            string[] primeurl = uri1.Split(del, StringSplitOptions.RemoveEmptyEntries);
            string odataurl = primeurl[0] + "//" + primeurl[1] + "/odata/" + primeurl[2] + "/" + primeurl[3] + "/" + primeurl[4] + "/" + primeurl[5] + "/$metadata";

            return odataurl;
        }
        /// <summary>
        /// Converts REST API URL to v1 Scoring API URL
        /// </summary>
        /// <param name="uri1">REST API URI</param>
        /// <returns>Scoring REST API URI</returns>
        private string convertRestToScore(string uri1)
        {
            string[] del = { "/" };
            string[] primeurl = uri1.Split(del, StringSplitOptions.RemoveEmptyEntries);
            string scoreurl = primeurl[0] + "//" + primeurl[1] + "/" + primeurl[2] + "/" + primeurl[3] + "/" + primeurl[4] + "/" + primeurl[5] + "/score";

            return scoreurl;
        }
        /// <summary>
        /// set global URI and auth key parameters with the data passed in the function
        /// </summary>
        /// <param name="uri">REST API Endpoint address</param>
        /// <param name="key">API Auth key </param>
        /// <returns>None.</returns>
        private void setUriAndKey(string uri, string key)
        {
            if (uri != "")
            {
                // if valid URI, save REST API URI along with a help URI 
                helpuri = convertRestToOdata(uri);
                predictionuri = convertRestToScore(uri);

            }
            if (key != "")
            { // update authkey if valid
                authkey = key;
            }
        }

        /// <summary>
        /// Retrieves prediction based on if input is arranged as rowwise or column wise as determined by variable setting rowwise
        /// </summary>
        /// <param name="tmpjs">Json Input Schema</param>
        /// <returns>Prediction string</returns>
        private string predict(string tmpjs)
        {
            int rows = (rowwise) ? (inputRange.Rows.Count) : (inputRange.Columns.Count);
            // do prediction for each row in the input range
            for (int i = 0; i < rows; i++)
            {
                errval = "";
                doPredictions(tmpjs, i);
                // if error is received on the last response, return error
                if (errval != "") return errval;
            }
            // return the last output
            return outval;
        }
        #endregion

        #region public prediction functions

        /// <summary>
        /// Predict the output by calling the given Request/Response REST API endpoint. This function does so in 3 easy steps:
        /// Step 1: Call API help by appending help to the URL and retrieve expected schema
        /// Step 2: Create POST data in JSON format for retrieving prediction
        /// Step 3: Call RRS endpoint and retrieve response and return output as ',' delimited
        /// </summary>
        /// <param name="inp">The excel input range to be passed as input parameters to API.</param>
        /// <param name="uri">URI of the Request Response Endpoint.</param>
        /// <param name="key">The API key required for the prediction.</param>
        /// <param name="col">The number of output columns to be retrieved. This excludes columns representing input columns in the response</param>
        /// <returns>returns ',' output with prediction information as per API desired schema.</returns>
        public string azurePredict(Excel.Range inp, string uri, string key, int col)
        {
            int rows = 0;
            // set URI and key parameters
            setUriAndKey(uri, key);
            // initialize number of columns to be retrieved
            colnum = col;
            rowwise = true;
            // initialize the input columns
            inputRange = inp;
            outval = "";
            // retrieve the schema
            getJsonFromHttpHelp();

            //pick rows as rowcount for cells arranged rowwise and pick rows as columncount for cells arranged columnwise
            rows = (rowwise) ? (inputRange.Rows.Count) : (inputRange.Columns.Count);

            // do prediction for each row in the input range
            return (predict(jsonschema));
        }

        /// <summary>
        /// This function retrieves JSON schema by parsing API Help using URI and key 
        /// from the function parameters if included in the function call else uses the global URI and key variables
        /// </summary>
        /// <param name="uri">Optional parameter - URI of the Request Response Endpoint. Include it for manually crafted schema</param>
        /// <param name="key">Optional parameter - The API key required for the prediction. Include it for manually crafted schema</param>
        /// <returns>returns JSON schema extracted from the help API</returns>

        public string getJson(string uri = "", string key = "")
        {
            // initialize uri and key
            setUriAndKey(uri, key);
            // retrieve JSON schema from API help
            getJsonFromHttpHelp();

            return jsonschema;
        }
        /// <summary>
        /// Predict the output by calling the given Request/Response REST API endpoint. This function assumes schema is already available or previously retrieved.
        /// If you need to do the multiple predictions for same model, it is recommended to use this API instead of azurePredict. Since this API assumes schema is already available, 
        /// it only makes a single call to predict without calling help API for retrieving schema information
        /// Step 1: Create POST data in JSON format for retrieving prediction using the available schema
        /// Step 2: Call RRS endpoint and retrieve response and return output as ',' delimited
        /// </summary>
        /// <param name="inp">The excel input range to be passed as input parameters to API.</param>
        /// <param name="numcols">The number of output columns to be retrieved. This excludes columns representing input columns in the response</param>
        /// <param name="jsonSchema1">Optional parameter - Send manually crafted or previously retrieved JSON schema. Include it for manually crafted JSON schema</param>
        /// <param name="uri">Optional parameter - URI of the Request Response Endpoint. Include it for manually crafted schema</param>
        /// <param name="key">Optional parameter - The API key required for the prediction. Include it for manually crafted schema</param>
        /// <returns>returns ',' output with prediction information as per API desired schema.</returns>
        public string azureFastPredict(Excel.Range inp, int numcols = -1, string jsonSchema1 = "", string uri = "", string key = "")
        {
            string tmpjs = jsonschema;
            // copy URI and key to global variables
            setUriAndKey(uri, key);
            // if JSON schema received as parameter is valid, use it else use the one in global variable
            if ((jsonSchema1 != "") && (jsonSchema1.Trim() != ""))
            {
                tmpjs = jsonSchema1;
            }
            else
            {
                tmpjs = jsonschema;
            }
            //copy other input parameters to global variables
            colnum = numcols;
            rowwise = true;
            inputRange = inp;
            outval = "";
            return (predict(tmpjs));

        }

        /// <summary>
        /// Predict the multi row output by calling the given Request/Response REST API endpoint. This function assumes schema is already available or previously retrieved.
        /// If you need to do the multiple predictions for same model, it is recommended to use this API instead of azurePredict. Since this API assumes schema is already available, 
        /// it only makes a single call to predict without calling help API for retrieving schema information
        /// Step 1: Create POST data in JSON format for retrieving prediction using the available schema
        /// Step 2: Call RRS endpoint and retrieve response and return output as ',' delimited
        /// </summary>
        /// <param name="inp">The excel input range to be passed as input parameters to API.</param>

        public string azureFastPredictMultiRow(Excel.Range inp)
        {
            string tmpuri = predictionuri;
            string finalres;

            // create multirow prediction uri
            predictionuri += "multirow";

            finalres = azureFastPredict(inp);

            // restore old uri in context 
            predictionuri = tmpuri;

            return finalres;
        }
        /// <summary>
        /// Retrieves prediction just like azureFastPredict and also assumes input range is arranged as rowwise
        /// </summary>
        /// <param name="inp">The excel input range to be passed as input parameters to API.</param>
        /// <param name="numcols">The number of output columns to be retrieved. This excludes columns representing input columns in the response</param>
        /// <param name="jsonSchema1">Optional parameter - Send manually crafted or previously retrieved JSON schema. Include it for manually crafted JSON schema</param>
        /// <param name="uri">Optional parameter - URI of the Request Response Endpoint. Include it for manually crafted schema</param>
        /// <param name="key">Optional parameter - The API key required for the prediction. Include it for manually crafted schema</param>
        /// <returns>returns ',' output with prediction information as per API desired schema.</returns>

        public string vAzureFastPredict(Excel.Range inp, int numcols = -1, string jsonSchema1 = "", string uri = "", string key = "")
        {
            azureFastPredict(inp.Rows[1], numcols, jsonSchema1, uri, key);
            return outval;
        }
        /// <summary>
        /// Retrieves prediction just like azureFastPredict but with assumption that input range is arranged as columnwise
        /// </summary>
        /// <param name="inp">The excel input range to be passed as input parameters to API.</param>
        /// <param name="numcols">The number of output columns to be retrieved. This excludes columns representing input columns in the response</param>
        /// <param name="jsonSchema1">Optional parameter - Send manually crafted or previously retrieved JSON schema. Include it for manually crafted JSON schema</param>
        /// <param name="uri">Optional parameter - URI of the Request Response Endpoint. Include it for manually crafted schema</param>
        /// <param name="key">Optional parameter - The API key required for the prediction. Include it for manually crafted schema</param>
        /// <returns>returns ',' output with prediction information as per API desired schema.</returns>

        public string hAzureFastPredict(Excel.Range inp, int numcols = -1, string jsonSchema1 = "", string uri = "", string key = "")
        {
            rowwise = false;
            string tmpjs = jsonschema;
            // copy URI and key to global variables
            setUriAndKey(uri, key);
            // if JSON schema received as parameter is valid, use it else use the one in global variable
            if ((jsonSchema1 != "") && (jsonSchema1.Trim() != ""))
            {
                tmpjs = jsonSchema1;
            }
            else
            {
                tmpjs = jsonschema;
            }
            //copy other input parameters to global variables
            colnum = numcols;

            inputRange = inp;
            outval = "";
            return (predict(tmpjs));

        }

        #endregion

        #region comfunc
        [ComRegisterFunction]
        public static void RegisterFunction(Type type)
        {
            // Add the "Programmable" registry key under CLSID 
            Registry.ClassesRoot.CreateSubKey(
                GetCLSIDSubKeyName(type, "Programmable"));


            // Register the full path to mscoree.dll which makes Excel happier. 
            RegistryKey key = Registry.ClassesRoot.OpenSubKey(
                GetCLSIDSubKeyName(type, "InprocServer32"), true);
            key.SetValue("",
                System.Environment.SystemDirectory + @"\mscoree.dll",
                RegistryValueKind.String);
        }
        private static string GetCLSIDSubKeyName(Type type, string subKeyName)
        {
            System.Text.StringBuilder s = new System.Text.StringBuilder();
            s.Append(@"CLSID\{");
            s.Append(type.GUID.ToString().ToUpper());
            s.Append(@"}\");
            s.Append(subKeyName);
            return s.ToString();
        }

        [ComUnregisterFunction]
        public static void UnregisterFunction(Type type)
        {
            // Remove the "Programmable" registry key under CLSID 
            Registry.ClassesRoot.DeleteSubKey(
                GetCLSIDSubKeyName(type, "Programmable"), false);
        }

        #endregion
    }
}
